"""
完整数据导入脚本 - 导入教师、学生、课程和成绩数据
"""
import sys
import os
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from dotenv import load_dotenv
load_dotenv()

from app import create_app
from app.extensions import db
from app.models.user import User, StudentProfile, TeacherProfile
from app.models.course import Course, CourseTag
from app.models.student_course import StudentCourse
from datetime import datetime
import pandas as pd


def import_teachers(app):
    """导入教师数据"""
    print("\n" + "="*60)
    print("导入教师数据")
    print("="*60)
    
    with app.app_context():
        try:
            df = pd.read_csv('data/teachers.csv')
            success_count = 0
            fail_count = 0
            
            for idx, row in df.iterrows():
                try:
                    # 检查用户是否已存在
                    existing_user = User.query.filter_by(username=row['username']).first()
                    if existing_user:
                        print(f"⚠ 教师 {row['username']} 已存在，跳过")
                        continue
                    
                    # 创建用户账号
                    user = User(
                        username=row['username'],
                        email=row['email'],
                        role='teacher',
                        first_name=row['first_name'],
                        last_name=row['last_name'],
                        phone=row['phone']
                    )
                    user.set_password(row['password'])
                    db.session.add(user)
                    db.session.flush()
                    
                    # 创建教师档案
                    profile = TeacherProfile(
                        user_id=user.id,
                        employee_id=row['employee_id'],
                        department=row['department'],
                        title=row['title'],
                        bio=row['bio']
                    )
                    db.session.add(profile)
                    success_count += 1
                    
                    if (idx + 1) % 10 == 0:
                        print(f"✓ 已导入 {idx + 1} 位教师...")
                        
                except Exception as e:
                    fail_count += 1
                    print(f"✗ 第 {idx+2} 行失败: {str(e)}")
                    continue
            
            db.session.commit()
            print(f"\n✓ 教师数据导入完成: 成功 {success_count} 条, 失败 {fail_count} 条")
            
        except Exception as e:
            db.session.rollback()
            print(f"✗ 导入教师数据失败: {str(e)}")
            raise


def import_students(app):
    """导入学生数据"""
    print("\n" + "="*60)
    print("导入学生数据")
    print("="*60)
    
    with app.app_context():
        try:
            df = pd.read_csv('data/students.csv')
            success_count = 0
            fail_count = 0
            
            for idx, row in df.iterrows():
                try:
                    # 检查用户是否已存在
                    existing_user = User.query.filter_by(username=row['username']).first()
                    if existing_user:
                        continue
                    
                    # 创建用户账号
                    user = User(
                        username=row['username'],
                        email=row['email'],
                        role='student',
                        first_name=row['first_name'],
                        last_name=row['last_name'],
                        phone=row['phone']
                    )
                    user.set_password(row['password'])
                    db.session.add(user)
                    db.session.flush()
                    
                    # 创建学生档案
                    profile = StudentProfile(
                        user_id=user.id,
                        student_id=row['student_id'],
                        major=row['major'],
                        grade_level=int(row['grade_level']),
                        enrollment_date=pd.to_datetime(row['enrollment_date']).date()
                    )
                    db.session.add(profile)
                    success_count += 1
                    
                    if (idx + 1) % 100 == 0:
                        print(f"✓ 已导入 {idx + 1} 位学生...")
                        db.session.commit()
                        
                except Exception as e:
                    fail_count += 1
                    if fail_count <= 5:  # 只显示前5个错误
                        print(f"✗ 第 {idx+2} 行失败: {str(e)}")
                    continue
            
            db.session.commit()
            print(f"\n✓ 学生数据导入完成: 成功 {success_count} 条, 失败 {fail_count} 条")
            
        except Exception as e:
            db.session.rollback()
            print(f"✗ 导入学生数据失败: {str(e)}")
            raise


def import_courses(app):
    """导入课程数据"""
    print("\n" + "="*60)
    print("导入课程数据")
    print("="*60)
    
    with app.app_context():
        try:
            df = pd.read_csv('data/courses.csv')
            success_count = 0
            fail_count = 0
            
            for idx, row in df.iterrows():
                try:
                    # 检查课程是否已存在
                    existing_course = Course.query.filter_by(code=row['code']).first()
                    if existing_course:
                        print(f"⚠ 课程 {row['code']} 已存在，跳过")
                        continue
                    
                    # 查找授课教师
                    instructor = User.query.filter_by(
                        username=row['instructor_username'],
                        role='teacher'
                    ).first()
                    
                    if not instructor:
                        print(f"⚠ 未找到教师 {row['instructor_username']}，跳过课程 {row['code']}")
                        fail_count += 1
                        continue
                    
                    # 创建课程
                    course = Course(
                        code=row['code'],
                        name=row['name'],
                        description=row['description'],
                        credits=float(row['credits']),
                        department=row['department'],
                        instructor_id=instructor.id,
                        semester=row['semester'],
                        max_students=int(row['max_students'])
                    )
                    db.session.add(course)
                    db.session.flush()
                    
                    # 添加课程标签
                    if pd.notna(row['tags']):
                        tags = row['tags'].split(',')
                        for tag_name in tags:
                            tag = CourseTag(
                                course_id=course.id,
                                tag=tag_name.strip()
                            )
                            db.session.add(tag)
                    
                    success_count += 1
                    
                except Exception as e:
                    fail_count += 1
                    print(f"✗ 第 {idx+2} 行失败: {str(e)}")
                    continue
            
            db.session.commit()
            print(f"\n✓ 课程数据导入完成: 成功 {success_count} 条, 失败 {fail_count} 条")
            
        except Exception as e:
            db.session.rollback()
            print(f"✗ 导入课程数据失败: {str(e)}")
            raise


def import_grades(app):
    """导入成绩数据（包含选课记录）"""
    print("\n" + "="*60)
    print("导入成绩数据")
    print("="*60)
    
    with app.app_context():
        try:
            df = pd.read_csv('data/grades.csv')
            success_count = 0
            fail_count = 0
            
            print(f"总共需要导入 {len(df)} 条成绩记录...")
            
            for idx, row in df.iterrows():
                try:
                    # 查找学生档案
                    student_profile = StudentProfile.query.filter_by(
                        student_id=row['student_id']
                    ).first()
                    
                    if not student_profile:
                        if fail_count < 5:
                            print(f"✗ 未找到学生 {row['student_id']}")
                        fail_count += 1
                        continue
                    
                    # 查找课程
                    course = Course.query.filter_by(code=row['course_code']).first()
                    if not course:
                        if fail_count < 5:
                            print(f"✗ 未找到课程 {row['course_code']}")
                        fail_count += 1
                        continue
                    
                    # 检查是否已有选课记录
                    existing = StudentCourse.query.filter_by(
                        student_id=student_profile.id,
                        course_id=course.id
                    ).first()
                    
                    if existing:
                        continue
                    
                    # 创建选课记录
                    student_course = StudentCourse(
                        student_id=student_profile.id,
                        course_id=course.id,
                        enrollment_date=pd.to_datetime(row['enrollment_date']).date(),
                        status=row['status'],
                        score=float(row['score']) if pd.notna(row['score']) else None,
                        midterm_score=float(row['midterm_score']) if pd.notna(row['midterm_score']) else None,
                        final_score=float(row['final_score']) if pd.notna(row['final_score']) else None,
                        total_classes=int(row['total_classes']) if pd.notna(row['total_classes']) else None,
                        attended=int(row['attended']) if pd.notna(row['attended']) else None,
                        attendance_rate=float(row['attendance_rate']) if pd.notna(row['attendance_rate']) else None,
                        graded_at=datetime.utcnow() if row['status'] == 'completed' else None
                    )
                    db.session.add(student_course)
                    success_count += 1
                    
                    # 每100条提交一次
                    if (idx + 1) % 100 == 0:
                        db.session.commit()
                        print(f"✓ 已导入 {idx + 1}/{len(df)} 条成绩记录...")
                        
                except Exception as e:
                    if fail_count < 10:
                        print(f"✗ 第 {idx+2} 行失败: {str(e)}")
                    fail_count += 1
                    continue
            
            db.session.commit()
            print(f"\n✓ 成绩数据导入完成: 成功 {success_count} 条, 失败 {fail_count} 条")
            
        except Exception as e:
            db.session.rollback()
            print(f"✗ 导入成绩数据失败: {str(e)}")
            raise


def main():
    """主函数"""
    print("\n" + "="*60)
    print("开始导入所有数据")
    print("="*60)
    
    app = create_app('development')
    
    try:
        # 按顺序导入数据
        import_teachers(app)
        import_students(app)
        import_courses(app)
        import_grades(app)
        
        print("\n" + "="*60)
        print("✓ 所有数据导入完成！")
        print("="*60)
        
        # 显示统计信息
        with app.app_context():
            teacher_count = User.query.filter_by(role='teacher').count()
            student_count = User.query.filter_by(role='student').count()
            course_count = Course.query.count()
            grade_count = StudentCourse.query.count()
            
            print(f"\n数据库统计:")
            print(f"  - 教师: {teacher_count} 位")
            print(f"  - 学生: {student_count} 位")
            print(f"  - 课程: {course_count} 门")
            print(f"  - 选课/成绩记录: {grade_count} 条")
        
    except Exception as e:
        print(f"\n✗ 数据导入失败: {str(e)}")
        sys.exit(1)


if __name__ == '__main__':
    main()












